import logging
import math
import time
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

ts_team_router = APIRouter(prefix="/tsTeam", tags=["团队资料管理"], dependencies=[Depends(oauth2_scheme)])


class TeamQuerySearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fteamname: Optional[str] = Field("", description="团队名称(支持前缀匹配)")


@ts_team_router.get("/list", summary="团队资料查询接口")
def list_ts_team(search: TeamQuerySearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from m_ts_team where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fteamname != "":
        sql = sql + " and fteamname like concat('%', :fteamname ,'%')"
        param["fteamname"] = search.fteamname
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fteamno,fteamname,fsite_no,fperson,fmemo,fstate,fupddate,foperator"
    sql = sql + " from m_ts_team where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fteamname != "":
        sql = sql + " and fteamname like concat('%', :fteamname ,'%')"
        param["fteamname"] = search.fteamname
    if search.order == "desc":
        sql = sql + " order by  fteamno desc "
    else:
        sql = sql + " order by  fteamno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fteamno'] = data[key][0]
        d1['fteamname'] = data[key][1]
        d1['fsiteno'] = data[key][2]
        d1['fperson'] = data[key][3]
        d1['fmemo'] = data[key][4]
        d1['fstate'] = data[key][5]
        d1['fupddate'] = data[key][6]
        d1['foperator'] = data[key][7]
        data_list.append(d1)
    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class TeamCreate(BaseModel):
    """团队资料新增请求参数"""
    fteamno: str = Field(..., description="团队编号")
    fteamname: str = Field(..., description="团队名称")
    fsiteNo: str = Field(..., description="站点编号")
    fperson: str = Field(..., description="负责人ID")
    fstate: int = Field(..., description="状态编码")
    fmemo: Optional[str] = Field("", description="备注说明")


@ts_team_router.post("/create", summary="团队资料新增接口")
def create_ts_team(create: TeamCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fteamno from m_ts_team where fteamno= :fteamno and fsite_No= :fsiteNo "
            data = db.execute(text(sql), {
                "fteamno": create.fteamno,
                "fsiteNo": create.fsiteNo,
            }).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="团队编号已存在")
            else:
                sql = "insert into m_ts_team(fteamno,fteamname,fsite_no,fperson,fmemo,fstate,fupddate,foperator)"
                sql = sql + " values( :fteamno , :fteamname , :fsiteNo , :fperson , :fmemo , :fstate ,now(), :userid );"
                db.execute(text(sql), {
                    "fteamno": create.fteamno,
                    "fteamname": create.fteamname,
                    "fsiteNo": create.fsiteNo,
                    "fperson": create.fperson,
                    "fmemo": create.fmemo,
                    "fstate": create.fstate,
                    "userid": user_id,
                })
                # 返回
                current_datetime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
                d1 = {}
                d1['fteamno'] = create.fteamno
                d1['fteamname'] = create.fteamname
                d1['fsiteno'] = create.fsiteNo
                d1['fperson'] = create.fperson
                d1['fmemo'] = create.fmemo
                d1['fstate'] = create.fstate
                d1['fupddate'] = current_datetime
                d1['foperator'] = user_id
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@ts_team_router.post("/update", summary="团队资料修改接口")
def update_ts_team(update: TeamCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update m_ts_team"
            sql = sql + " set fteamname= :fteamname ,fperson= :fperson "
            sql = sql + ",fmemo= :fmemo ,fstate= :fstate "
            sql = sql + " ,fupddate=now(),foperator= :userid "
            sql = sql + " where fteamno= :fteamno  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fteamname": update.fteamname,
                "fperson": update.fperson,
                "fmemo": update.fmemo,
                "fstate": update.fstate,
                "userid": user_id,
                "fteamno": update.fteamno,
                "fsiteNo": update.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@ts_team_router.post("/delete/{fteamno}", summary="团队资料删除接口")
def delete_ts_team(fteamno: str = Path(description="团队资料编号"), db: Session = Depends(get_db)):
    try:
        param = {"fteamno": fteamno}
        with db.begin():
            sql = "select count(1) from m_ts_task where fteamno= :fteamno "
            data = db.execute(text(sql), param).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from m_ts_team where fteamno= :fteamno "
                db.execute(text(sql), param)
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
